package com.example.ruteroelmeromero;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class BaseDatos extends SQLiteOpenHelper {

	public static final String DATABASE_NAME = "rutero.db";
	Context context;
	SQLiteDatabase db;

	public BaseDatos(Context context) {
		super(context, DATABASE_NAME, null, 1);
		this.context = context;
		SQLiteDatabase db = this.getWritableDatabase();
		onCreate(db);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		AssetManager am = context.getAssets();
		try {
			// borrar tablas
			db.execSQL("drop table if exists producto;");
			db.execSQL("drop table if exists venta;");
			db.execSQL("drop table if exists venta_detalle;");
			InputStream is = am.open("base.sql");
			String ejecuta = leerArchivo(is);
			for (String token : ejecuta.split(";")) {
				if (token.trim().length() > 0)
					db.execSQL(token.trim());
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}

	private String leerArchivo(InputStream arch) {
		String retorno = "";
		StringBuffer buff = new StringBuffer("");
		try {
			int ch;
			while ((ch = arch.read()) != -1)
				buff.append((char) ch);
			arch.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		retorno = buff.toString();
		return retorno;
	}

	public List<Map> listar(int numero, double lat, double lng) {
		List<Map> ret = new ArrayList<Map>();
		Map valor;
		try {
			String qry = "SELECT v.lat,v.lng,SUM(vd.cantidad*p.precio) as ubicacion,v.tienda FROM venta v, venta_detalle vd, producto p WHERE p.producto = vd.producto AND v.venta = vd.venta GROUP BY v.lat,v.lng";
			Cursor c = this.getReadableDatabase().rawQuery(qry, null);
			if(c != null && c.moveToFirst()){
				do{
					valor = new HashMap();
					valor.put("lat", new Double(c.getDouble(0)));
					valor.put("lng", new Double(c.getDouble(1)));
					valor.put("ubicacion", c.getString(2));
					valor.put("tienda", c.getString(3));
					ret.add(valor);
				}while(c.moveToNext());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	public List<Map> listarTodo() {
		List<Map> ret = new ArrayList<Map>();
		Map valor;
		try {
			String qry = "SELECT sum(vd.cantidad),sum(p.precio),p.nombre FROM venta v, venta_detalle vd, producto p WHERE p.producto = vd.producto AND v.venta = vd.venta GROUP BY p.nombre";
			Cursor c = this.getReadableDatabase().rawQuery(qry, null);
			if(c != null && c.moveToFirst()){
				do{
					valor = new HashMap();
					valor.put("cantidad", c.getString(0));
					valor.put("precio", c.getString(1));
					valor.put("producto", c.getString(2));
					valor.put("total", String.valueOf(c.getDouble(0) * c.getDouble(1)));
					ret.add(valor);
				}while(c.moveToNext());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
}
